/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.TreeSet;

/**
 *
 * @author sio
 */
public class PaysDAO {
    
    static  Collection <Dep> getLesDeps(){

        try{

            Connection con = Connect.get();
            Statement reqD;
            reqD = con.createStatement();
            ResultSet rsD = reqD.executeQuery("select distinct departement from medecin");
            Collection <Dep> d = new TreeSet <Dep> ();

            //Parcours
            
            Collection <Med> m;
            
            while (rsD.next()) { 
                m = new TreeSet <Med> ();
                Statement reqM = con.createStatement();
                ResultSet rsM = reqM.executeQuery("select * from medecin"
                        + " where departement = " + rsD.getString("departement"));
                
                while (rsM.next()) {
                    m.add(new Med(rsM.getString("nom"), rsM.getString("prenom"), rsM.getString("adresse"), 
                            rsM.getString("tel"), rsM.getString("specialitecomplementaire"), rsM.getString("id")));
                    
                }
                rsM.close();
                Dep unDep = new Dep(rsD.getString("departement"), m);
                d.add(unDep);
            }
            return d;
        }
        
        catch(SQLException e){
            System.out.println("Erreur SQL :" + e);
             return null;
        }
    }
}
